using System;
using System.Collections;
using System.Data.SqlClient;
using System.Xml;
using LocalBoard.Persistence.SqlAccessor;
using Microsoft.ApplicationBlocks.Data;

namespace LocalBoard.Persistence.SqlAccessor
{
	/// <summary>
	/// Summary description for SqlClubProfileAccessor.
	/// </summary>
	public class SqlClubProfileAccessor : SqlDataAccessBase, IClubProfileAccessor
	{
		public static readonly SqlClubProfileAccessor Instance = new SqlClubProfileAccessor();

		private SqlClubProfileAccessor()
		{
		}

		/// <summary>
		/// Check if the user is a member of the club or not
		/// </summary>
		/// <param name="id">User's profile id</param>
		/// <param name="clubId">Club id</param>
		/// <returns>True if user is a memeber of the club, False otherwise</returns>
		public bool IsUserInTheClub(int id, int clubId)
		{
			SqlDataReader dataReader = null;
			try
			{
				dataReader = SqlHelper.ExecuteReader(ConnectionString, "GetUserClubProfileByProfileIdAndClubId", id, clubId);
				// Execute the stored procedure
				if(dataReader.Read())
					return true;
			}
			catch (Exception ex )
			{
				// Log error details
				throw ex;
			}
			finally
			{
				if(dataReader != null) dataReader.Close();
			}
			return false;
		}

		/// <summary>
		/// Get all user list and users' club profiles in a club
		/// </summary>
		/// <param name="clubId">club id</param>
		/// <returns>Club profile xml node object hashtable with profile id as a key</returns>
		public Hashtable GetAllUserClubProfiles(int clubId)
		{
			Hashtable clubProfiles = new Hashtable();
			SqlDataReader dataReader = null;
			try
			{
				dataReader = SqlHelper.ExecuteReader(ConnectionString, "GetAllUserClubProfileByClubId", clubId);
				// Execute the stored procedure
				while(dataReader.Read())
				{
					XmlDocument doc = null;
					string clubProfileStr = DataConverter.GetString(dataReader["ClubProfile"]);
					if(clubProfileStr != null && clubProfileStr.Length > 0)
					{
						doc = new XmlDocument();
						doc.LoadXml(clubProfileStr);
					}
					clubProfiles.Add(DataConverter.GetInteger(dataReader["Profile_Id"]), doc);
				}
			}
			catch (Exception ex )
			{
				// Log error details
				throw ex;
			}
			finally
			{
				if(dataReader != null) dataReader.Close();
			}
			return clubProfiles;
		}

		/// <summary>
		/// Get all club list that user joined
		/// </summary>
		/// <param name="profileId">profile id</param>
		/// <returns>Club list</returns>
		public ArrayList GetAllClubsThatUserJoined(int profileId)
		{
			ArrayList ar = new ArrayList();
			SqlDataReader dataReader = null;
			try
			{
				dataReader = SqlHelper.ExecuteReader(ConnectionString, "GetAllClubsThatUserJoined", profileId);
				// Execute the stored procedure
				while(dataReader.Read())
				{
					ar.Add(DataConverter.GetInteger(dataReader["Club_Id"]));
				}
			}
			catch (Exception ex )
			{
				// Log error details
				throw ex;
			}
			finally
			{
				if(dataReader != null) dataReader.Close();
			}
			return ar;
		}

		/// <summary>
		/// Get club profile
		/// </summary>
		/// <param name="id">User profile id</param>
		/// <param name="clubId">Club id</param>
		/// <returns>Club profile as xml node object</returns>
		public XmlNode Get(int id, int clubId)
		{
			SqlDataReader dataReader = null;
			try
			{
				dataReader = SqlHelper.ExecuteReader(ConnectionString, "GetUserClubProfileByProfileIdAndClubId", id, clubId);
				// Execute the stored procedure
				if(dataReader.Read())
				{
					XmlDocument doc = null;
					string clubProfileStr = DataConverter.GetString(dataReader["ClubProfile"]);
					if(clubProfileStr != null && clubProfileStr.Length > 0)
					{
						doc = new XmlDocument();
						doc.LoadXml(clubProfileStr);
					}
					return doc;
				}
			}
			catch (Exception ex )
			{
				// Log error details
				throw ex;
			}
			finally
			{
				if(dataReader != null) dataReader.Close();
			}
			return null;
		}

		/// <summary>
		/// Register user as a club member and insert club profile
		/// </summary>
		/// <param name="id">User profile id to register to the club</param>
		/// <param name="clubProfile">Club profile as xml node object for insert</param>
		/// <param name="clubId">Club id</param>
		public void Insert(int id, XmlNode clubProfile, int clubId)
		{
			SqlHelper.ExecuteNonQuery(ConnectionString, "InsertUserClubProfile", id, clubProfile != null ? clubProfile.OuterXml : "", clubId);
		}

		/// <summary>
		/// Update user's club profile
		/// </summary>
		/// <param name="id">User profile id</param>
		/// <param name="clubProfile">Club profile as xml node object for update</param>
		/// <param name="clubId">Club id</param>
		public void Update(int id, XmlNode clubProfile, int clubId)
		{
			SqlHelper.ExecuteNonQuery(ConnectionString, "UpdateUserClubProfile", id, clubProfile != null ? clubProfile.OuterXml : "", clubId);
		}

		/// <summary>
		/// Unregister user from the club and delete club profile
		/// </summary>
		/// <param name="id">User profile id to unregister from the club</param>
		/// <param name="clubId">Club id</param>
		public void Delete(int id, int clubId)
		{
			SqlHelper.ExecuteNonQuery(ConnectionString, "DeleteUserClubProfile", id, clubId);
		}
	}
}
